create external table if not exists jms_ods.yl_oms_oms_order_hi (
    id                                    bigint             comment'订单编号'
   ,customer_order_id                     string             comment'客户订单编号'
   ,order_source_code                     string             comment'订单来源code'
   ,order_source_name                     string             comment'订单来源名称'
   ,order_type_code                       bigint             comment'订单类型code'
   ,waybill_id                            string             comment'运单号'
   ,origin_id                             bigint             comment'始发地id'
   ,origin_code                           string             comment'始发地code'
   ,origin_name                           string             comment'始发地名字'
   ,sender_name                           string             comment'寄件人姓名'
   ,sender_company                        string             comment'寄件人公司'
   ,sender_mobile_phone                   string             comment'寄件人手机号'
   ,sender_telphone                       string             comment'寄件人座机'
   ,sender_country_id                     bigint             comment'寄件国家id'
   ,sender_country_name                   string             comment'寄件国家名称'
   ,sender_province_id                    bigint             comment'寄件省份id'
   ,sender_province_name                  string             comment'寄件省份名称'
   ,sender_city_id                        bigint             comment'寄件城市id'
   ,sender_city_name                      string             comment'寄件城市名称'
   ,sender_area_id                        bigint             comment'寄件区域id'
   ,sender_area_name                      string             comment'寄件区域名称'
   ,sender_township                       string             comment'寄件乡镇'
   ,sender_street                         string             comment'寄件街道'
   ,sender_detailed_address               string             comment'寄件详细地址'
   ,sender_postal_code                    string             comment'寄件邮编'
   ,sender_email                          string             comment''
   ,is_privacy                            smallint           comment'隐私标记;1:是,0:否'
   ,phone_contact                         smallint           comment'电联标识;1:是,0:否'
   ,receiver_name                         string             comment'收件人姓名'
   ,receiver_company                      string             comment'收件人公司'
   ,receiver_mobile_phone                 string             comment'收件人手机号'
   ,receiver_telphone                     string             comment'收件人座机'
   ,destination_id                        bigint             comment'目的地id'
   ,destination_code                      string             comment'目的地code'
   ,destination_name                      string             comment'目的地名字'
   ,receiver_country_id                   bigint             comment'收件国家id'
   ,receiver_country_name                 string             comment'收件国家名称'
   ,receiver_province_id                  bigint             comment'收件省份id'
   ,receiver_province_name                string             comment'收件省份名称'
   ,receiver_city_id                      bigint             comment'收件城市id'
   ,receiver_city_name                    string             comment'收件城市名称'
   ,receiver_area_id                      bigint             comment'收件区域id'
   ,receiver_area_name                    string             comment'收件区域名称'
   ,receiver_township                     string             comment'收件乡镇'
   ,receiver_street                       string             comment'收件街道'
   ,receiver_detailed_address             string             comment'收件详细地址'
   ,receiver_postal_code                  string             comment'收件邮编'
   ,receiver_email                        string             comment''
   ,receiver_sorting_code                 string             comment'收件分拣码'
   ,terminal_dispatch_code                string             comment'三段码'
   ,accurate                              tinyint            comment'三段码是否保证准确,true:保证准确false:不保证准确'
   ,remarks                               string             comment''
   ,express_type_code                     string             comment'快件类型code'
   ,express_type_name                     string             comment'快件类型名称'
   ,insured                               smallint           comment'需要保价1是，0否'
   ,declared_value                        double             comment'货品声明价值'
   ,insured_value                         double             comment'保价费'
   ,cod_need                              smallint           comment'需要代收货款,1是，0否'
   ,cod_money                             double             comment'代收货款金额'
   ,cod_fee                               double             comment'代收货款手续费'
   ,cod_currency_type_code                string             comment'代收货款币别code'
   ,cod_currency_type_name                string             comment'代收货款币别名称'
   ,goods_type_code                       string             comment'物品类型code'
   ,goods_type_name                       string             comment'物品类型名称'
   ,goods_name                            string             comment'物品名称'
   ,package_number                        smallint           comment'件数,＞1表示子母件，如果业务上不支持子母件，则前端限制不显示，并默认为1'
   ,package_length                        double             comment'包裹总长,单位厘米'
   ,package_wide                          double             comment'包裹总宽,单位厘米'
   ,package_high                          double             comment'包裹总高,单位厘米'
   ,packate_volume                        double             comment'包裹体积重,单位立方厘米'
   ,package_charge_weight                 double             comment'包裹计费重量,单位千克'
   ,package_total_weight                  double             comment'包裹总重量,单位千克'
   ,box_standard_id                       bigint             comment'箱子规格id'
   ,box_standard_code                     string             comment'箱子规格code'
   ,box_standard_name                     string             comment'箱子规格'
   ,box_price                             double             comment'箱子价格'
   ,standard_value                        double             comment'标准运费'
   ,receivable_freight                    double             comment'应收运费'
   ,discount_freight                      double             comment'折扣运费'
   ,total_freight                         double             comment'总运费'
   ,payment_mode_code                     string             comment'付款方式code'
   ,payment_mode_name                     string             comment'付款方式名称'
   ,paid_mode_id                          smallint           comment'支付方式id'
   ,paid_mode_code                        string             comment'支付方式code'
   ,paid_mode_name                        string             comment'支付方式名称'
   ,customer_code                         string             comment'客户编号code'
   ,customer_name                         string             comment'客户编号名称'
   ,spm_money_type                        string             comment'结算货币类型'
   ,send_code                             string             comment'寄件服务方式code'
   ,send_name                             string             comment'寄件服务方式名称'
   ,dispatch_code                         string             comment'派件服务方式code'
   ,dispatch_name                         string             comment'派件服务方式名称'
   ,dispatch_number                       string             comment'调度单号'
   ,customer_order_time                   timestamp          comment'客户下单时间'
   ,input_time                            timestamp          comment'订单录入时间'
   ,order_status_code                     bigint             comment'订单状态code'
   ,cancel_reason                         string             comment'取消订单原因'
   ,cancel_time                           timestamp          comment'订单取消时间'
   ,pick_fail_time                        timestamp          comment'取件失败时间'
   ,pick_fail_reason                      string             comment'取件失败原因'
   ,prints_number                         smallint           comment'打印次数'
   ,best_pick_time_start                  timestamp          comment'最佳取件开始时间'
   ,best_pick_time_end                    timestamp          comment'最佳取件结束时间'
   ,real_pick_network_id                  bigint             comment'实际取件网点id'
   ,real_pick_network_code                string             comment'实际取件网点code'
   ,real_pick_network_name                string             comment'实际取件网点名称'
   ,dispatch_network_time                 timestamp          comment'调度网点时间'
   ,pick_network_id                       bigint             comment'取件网点id'
   ,pick_network_code                     string             comment'取件网点code'
   ,pick_network_name                     string             comment'取件网点名称'
   ,dispatch_staff_time                   timestamp          comment'调度业务员时间'
   ,pick_staff_code                       string             comment'取件业务员code'
   ,pick_staff_name                       string             comment'取件业务员名称'
   ,pick_time                             timestamp          comment'取件时间'
   ,last_dispatch_network_id              bigint             comment'上一次调度网点id'
   ,fourth_segment_code                   string             comment'上一次调度网点名称字段改名为四段码'
   ,last_dispatch_network_code            string             comment'上一次调度网点code'
   ,last_dispatch_network_time            timestamp          comment'上一次调度网点时间'
   ,dispatch_network_reason               string             comment'调度网点原因'
   ,last_dispatch_staff_code              string             comment'上一次调度业务员code'
   ,last_dispatch_staff_name              string             comment'上一次调度业务员名称'
   ,last_dispatch_staff_time              timestamp          comment'上一次调度业务员时间'
   ,assigner_name                         string             comment'委托人姓名'
   ,assigner_mobile_phone                 string             comment'委托人手机号'
   ,goods_value                           double             comment'物品价值'
   ,express_type_id                       bigint             comment'快件类型id'
   ,goods_type_id                         bigint             comment'物品类型id'
   ,payment_mode_id                       bigint             comment'付款方式id'
   ,box_number                            bigint             comment'箱子数量'
   ,member_id                             bigint             comment'会员id'
   ,sender_country_code                   string             comment'寄件国家三字码'
   ,receiver_country_code                 string             comment'收件国家三字码'
   ,customer_id                           bigint             comment'客户id'
   ,update_by                             bigint             comment'最后更新人id'
   ,update_by_name                        string             comment'更新人姓名'
   ,update_time                           timestamp          comment'更新时间'
   ,last_update_time_sync                 timestamp          comment'同步es更新时间'
   ,is_delete                             smallint           comment'是否删除,1未删除，2已删除'
   ,is_plaintext                          tinyint            comment''
   ,create_by                             bigint             comment'创建人id'
   ,create_by_code                        string             comment'创建人编码'
   ,create_by_name                        string             comment'创建人姓名'
   ,back_dispatch_network_number          bigint             comment'打回调度订单次数'
   ,back_dispatch_network_reason          string             comment'打回调度订单原因'
   ,back_dispatch_network_time            timestamp          comment'打回调度订单时间'
   ,settlement_weight                     double             comment'结算重量'
   ,dispatch_network_id                   bigint             comment'派件网点id'
   ,cancel_reason_code                    bigint             comment'订单取消原因编号'
   ,need_dispatch                         bigint             comment'是否需要调度1需要2不需要'
   ,proxy_area_id                         bigint             comment'代理区id'
   ,proxy_area_code                       string             comment'代理区code'
   ,proxy_area_name                       string             comment'代理区name'
   ,cancel_explain                        string             comment'取消订单说明'
   ,dispatch_proxy_area_time              timestamp          comment'调度代理区时间'
   ,is_real_name                          smallint           comment'是否实名制,1是，0否'
   ,real_name                             string             comment'实名姓名'
   ,id_no_type                            smallint           comment'证件类型'
   ,id_no                                 string             comment'证件号码'
   ,sex                                   smallint           comment'性别'
   ,exception_msg                         string             comment'异常信息'
   ,inspection_method                     string             comment''
   ,package_volume                        int                comment'包裹体积单位立方厘米'
   ,customer_waybill_no                   string             comment'客户运单号'
   ,is_business                           tinyint            comment'是否是商务件1是2否'
   ,print_status_code                     string             comment'云打印状态'
   ,second_code_hit_desc                  string             comment'二段码命中原因'
   ,dispatch_network_code                 string             comment'派件网点code'
   ,dispatch_network_name                 string             comment'派件网点名称'
   ,is_can_cancel                         bigint             comment'是否允许被极兔取消#1,不允许;0,允许'
   ,sign_receipt                          tinyint            comment'签回单0:否，1:是，2:回单'
   ,receipt_waybill_no                    string             comment'回单运单号'
   ,last_center_code                      string             comment'集包地编码'
   ,last_center_name                      string             comment'集包地名称'
   ,receipt_freight                       double             comment'回单金额'
   ,is_transfer                           tinyint            comment'是否转寄(1否2是)'
)comment  '订单表'
partitioned by (`dt` string comment 'create_time&update_time (yyyy-mm-dd)'))
stored as parquet
location '/dw/hive/jms_ods.db/external/yl_oms_oms_order_hi'
tblproperties (
'discover.partitions' = 'false',
'parquet.column.index.access' = 'true'
);
